﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
    public class Common
    {
        public static string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();

        #region 实现登录的方法
        public static T Login<T>(string name, string pwd)
        {
            string SqlText = "select {0} from [{1}] where Users_Account=@name and Users_Pwd=@pwd";
            Type type = typeof(T);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
            SqlText = string.Format(SqlText, SelectField, type.Name);
            var oObject = (T)Activator.CreateInstance(type);
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(SqlText, con);
                com.Parameters.Add(new SqlParameter("@name", name));
                com.Parameters.Add(new SqlParameter("@pwd", pwd));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    foreach (var pop in type.GetProperties())
                    {
                        object oValue = reader[pop.Name];
                        if (oValue is DBNull)
                        {
                            pop.SetValue(oObject, null);
                        }
                        else
                        {
                            pop.SetValue(oObject, oValue);
                        }
                    }
                }
            }
            return oObject;
        }
        #endregion

        #region 获取列表的方法
        public static List<T> GetList<T>()
        {
            string sql = "select {0} from [{1}]";
            Type type = typeof(T);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<T> list = new List<T>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    var oObject = (T)Activator.CreateInstance(type);
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(oObject, null);
                        }
                        else
                        {
                            item.SetValue(oObject, oValue);
                        }
                    }
                    list.Add(oObject);
                }
            }
            return list;
        }
        #endregion

        #region 添加
        public static int ADD<T>(T t)
        {
            string sqyj = "insert into [{0}] ({1}) values ({2})";
            Type type = typeof(T);
            sqyj = string.Format(sqyj,
                type.Name,
                string.Join(",", type.GetProperties().Where(p => p.Name != "ID").Select(x => string.Format("[{0}]", x.Name))),
                string.Join(",", type.GetProperties().Where(p => p.Name != "ID").Select(x => string.Format("@{0}", x.Name)))
                );
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sqyj, con);
                SqlParameter[] pamter = type.GetProperties().Where(p => !"Id".Equals(p.Name))
                    .Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(t) ?? DBNull.Value))
                    .ToArray();
                com.Parameters.AddRange(pamter);
                return com.ExecuteNonQuery();
            }
        }
        #endregion

        #region 删除
        //通用删除
        public static bool DeleteEntity<T>(int id)
        {
            string sql = "delete from [{0}] where id = @id";
            Type type = typeof(T);

            sql = string.Format(sql, type.Name);
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.Add(new SqlParameter("@id", id));
                return com.ExecuteNonQuery() > 0;
            }
        }

        #endregion

        #region  通用修改
        public static bool UpdateEntity<T>(T t)
        {
            string sql = "update [{0}] set {1} where ID= @ID ";
            Type type = typeof(T);
            //拼接SQL
            sql = string.Format(sql,
                type.Name,
                string.Join(",", type.GetProperties()
                .Where(p => p.Name != "ID")
                .Select(x => string.Format("[{0}]=@{1}", x.Name, x.Name))));

            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                SqlParameter[] pamter = type.GetProperties()
                    .Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(t) ?? DBNull.Value)).ToArray();
                com.Parameters.AddRange(pamter);
                return com.ExecuteNonQuery() > 0;
            }
        }

  

        #endregion

        #region  根据ID 进行查询
        public static T ListByid<T>(int id)
        {
            string SqlText = "select {0} from [{1}] where ID = @id";
            Type type = typeof(T);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
            SqlText = string.Format(SqlText, SelectField, type.Name);
            var oObject = (T)Activator.CreateInstance(type);
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(SqlText, con);
                com.Parameters.Add(new SqlParameter("@id", id));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    foreach (var pop in type.GetProperties())
                    {
                        object oValue = reader[pop.Name];
                        if (oValue is DBNull)
                        {
                            pop.SetValue(oObject, null);
                        }
                        else
                        {
                            pop.SetValue(oObject, oValue);
                        }
                    }
                }
            }
            return oObject;
        }
        #endregion

        #region 存储过程分页显示
        /// <summary>
        /// 存储分页i显示
        /// </summary>
        /// <returns></returns>
        public static List<T> Paging<T>(string tableName, string fileName, int pageindex, int pagesize, out int pagecount)
        {
            Type type = typeof(T);
            List<T> list = new List<T>();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("sp_pager", conn);//第一个参数是存储过程名称  第二个是连接字符串
                cmd.CommandType = CommandType.StoredProcedure;//指定上一行cmd执行的是存储过程
                SqlParameter[] para ={
                                   new SqlParameter("@table",SqlDbType.NChar),
                                   new SqlParameter("@primarykey_id",SqlDbType.NChar),
                                   new SqlParameter("@index",SqlDbType.Int),
                                   new SqlParameter("@pagesize",SqlDbType.Int),
                                   new SqlParameter("@pagecount",SqlDbType.Int),
                               };
                para[0].Value = tableName;
                para[1].Value = fileName;
                para[2].Value = pageindex;
                para[3].Value = pagesize;
                //指定参数  为输出参数     
                para[4].Direction = ParameterDirection.Output;
                //向cmd传递参数
                cmd.Parameters.AddRange(para);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //获取要输出的参数
                pagecount = Convert.ToInt32(para[4].Value);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var oObject = (T)Activator.CreateInstance(type);
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(oObject, null);
                        }
                        else
                        {
                            item.SetValue(oObject, oValue);
                        }
                    }
                    list.Add(oObject);
                }
            }
            return list;
        }

        #endregion

        #region 显示查询
        public static string GetTable<T>(string TableName, params SqlParameter[] Parameter)
        {
            string sql = "";
            if (Parameter.Length != 0)
            {
                sql = "select * from {0} where 1=1 {1}";
                string where = "";
                Type type = typeof(T);
                foreach (var item in type.GetProperties())
                {
                    foreach (var p in Parameter)
                    {
                        if ('@' + item.Name == p.ParameterName)
                        {
                            if (item.PropertyType == typeof(string))
                            {
                                where += " and " + item.Name + "='" + p.Value + "'";
                            }
                            else
                            {
                                where += " and " + item.Name + "=" + p.Value;
                            }
                        }
                    }
                }
                return string.Format(sql, TableName, where);
            }
            else
            {
                sql = "select * from {0} ";
                return string.Format(sql, TableName);
            }


        }

        #endregion

        #region 返回DataTable对象
        /// <summary>
        /// 返回DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramters"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string sql, params SqlParameter[] paramters)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    DataSet ds = new DataSet();
                    SqlCommand command = new SqlCommand(sql, conn);
                    command.Parameters.AddRange(paramters);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(ds);
                    return ds.Tables[0];

                }
                catch (SqlException e)
                {

                    throw new Exception(e.Message);
                }
            }
        }
        #endregion
    }
}
